﻿go
print 'create PROCEDURE dbo.RepSvedSem'
go
create PROCEDURE dbo.RepSvedSem
	(
		@D_S datetime,	
		@D_PO datetime,
		@ERROR_MSG varchar(1024) = '' OUTPUT 	-- строка с текстом ошибки. Если ошибка отсуствовала, то пустая
	)
AS
-- Отчет "Сведения о семьях"
BEGIN

BEGIN TRY
	BEGIN TRAN
	
	DECLARE @K_POLGOD int
	DECLARE @N_HEADER varchar(1024)
	DECLARE @N_RUK varchar(1024)
	DECLARE @N_ISPOLN varchar(1024)
	

	SET @N_HEADER =  'Отчет ' + 
		isnull((Select top 1 PARAM_VALUE from _params_user where N_PARAM_NAME = 'NameOrgPr'),'') +
		' о работе с семьей на ' + dbo.DateToStringPadezh(DATEADD(day, 1, @D_PO))
		
	SET @N_RUK = isnull((Select top 1 PARAM_VALUE from _params_user where N_PARAM_NAME = 'DirecrotFio'),'')
	
	SET @N_ISPOLN = isnull((Select top 1 PARAM_VALUE from _params_user where N_PARAM_NAME = 'OtvFio'),'') + ', ' + 
		isnull((Select top 1 PARAM_VALUE from _params_user where N_PARAM_NAME = 'OtvGlg'),'')+ ', ' +
		isnull((Select top 1 PARAM_VALUE from _params_user where N_PARAM_NAME = 'OtvTel'),'')
		
	
	-- Формируем основную запись
	--
	INSERT INTO bo_polgod (D_S, D_PO, N_RUK, N_ISPOLN, N_HEADER) VALUES 
		(@D_S, @D_PO, @N_RUK, @N_ISPOLN, @N_HEADER)

	SELECT @K_POLGOD = SCOPE_IDENTITY();
	
	EXECUTE RepSvedSemR1 @D_S, @D_PO, @K_POLGOD
	
	EXECUTE RepSvedSemR3 @D_S, @D_PO, @K_POLGOD

END TRY
BEGIN CATCH
	
	set @ERROR_MSG = isnull(dbo.GetLastError(),'')
	ROLLBACK TRANSACTION
	RETURN -1
	
END CATCH;
	COMMIT TRAN
	RETURN 0
END



go
print 'create PROCEDURE dbo.RepSvedSemR1'
go
create PROCEDURE dbo.RepSvedSemR1 
	(
		@D_S datetime,	
		@D_PO datetime,
		@K_POLGOD int
	)
AS
-- Отчет "Сведения о семьях" 
-- Раздел 1
BEGIN
	
	-- Выбираем данные по категориям семей
	DECLARE KatSemGroupCursor cursor LOCAL READ_ONLY FOR
	SELECT K_KAT_SEM_GRUP, N_KAT_SEM_GRUP, C_SORT FROM s_kat_sem_grup ORDER BY C_SORT

	DECLARE @N_KAT_SEM_GRUP varchar(1024)
	DECLARE @K_KAT_SEM_GRUP int 
	DECLARE @C_SORT_GRUP int
	
	-- Данные по группе
	DECLARE @C_SEM_VSEGO_GRUP int
	DECLARE @C_DETI_VSEGO_GRUP int
	DECLARE @C_DETI_SCHOOL_GRUP int
	
	-- Данные по всем группам
	DECLARE @C_SEM_VSEGO_ALL int
	DECLARE @C_DETI_VSEGO_ALL int
	DECLARE @C_DETI_SCHOOL_ALL int
	
	SET @C_SEM_VSEGO_ALL = 0
	SET @C_DETI_VSEGO_ALL = 0
	SET @C_DETI_SCHOOL_ALL = 0
	
	OPEN KatSemGroupCursor			
	FETCH NEXT FROM KatSemGroupCursor into @K_KAT_SEM_GRUP, @N_KAT_SEM_GRUP, @C_SORT_GRUP
	
	-- Цикл по группам категорий
	while @@FETCH_STATUS = 0
	BEGIN
		SET @C_SEM_VSEGO_GRUP = 0
		SET @C_DETI_VSEGO_GRUP = 0
		SET @C_DETI_SCHOOL_GRUP = 0
		
		DECLARE KatSemCursor cursor LOCAL STATIC READ_ONLY FOR
		-- Рассчет данных 
		select s_kat_sem.N_KAT_SEM, C_SEM_VSEGO, C_DETI_VSEGO, C_DETI_SCHOOL 
		from
		s_kat_sem
		FULL JOIN
		(
			-- Рассчет данных "кол-во семей"
			SELECT     K_KAT_SEM, COUNT(DISTINCT b_uks.K_UKS) AS C_SEM_VSEGO
			FROM         b_uks 
					INNER JOIN sv_uks_kat_sem ON b_uks.K_UKS = sv_uks_kat_sem.K_UKS
			WHERE     (b_uks.D_DZ BETWEEN @D_S AND @D_PO) AND (sv_uks_kat_sem.F_OTCHET = 1)
			GROUP BY K_KAT_SEM
			) as t_count_uks ON s_kat_sem.K_KAT_SEM = t_count_uks.K_KAT_SEM
		FULL JOIN
		(
			-- Рассчет данных "кол-во детей в семьях" (vUksDety)
			SELECT     K_KAT_SEM, COUNT(vUksDeti.K_SOSTAV_SEMYI) AS C_DETI_VSEGO
			FROM         b_uks 
					INNER JOIN sv_uks_kat_sem ON b_uks.K_UKS = sv_uks_kat_sem.K_UKS
					INNER JOIN vUksDeti ON b_uks.K_UKS = vUksDeti.K_UKS 			
			WHERE     (b_uks.D_DZ BETWEEN @D_S AND @D_PO) AND (sv_uks_kat_sem.F_OTCHET = 1)
			GROUP BY K_KAT_SEM
		) as t_count_deti ON s_kat_sem.K_KAT_SEM = t_count_deti.K_KAT_SEM 
		FULL JOIN
		(
			-- Рассчет данных "кол-во детей школьного возраста в семьях (с 6 по 18 лет) 
			SELECT     K_KAT_SEM, COUNT(vUksDeti.K_SOSTAV_SEMYI) AS C_DETI_SCHOOL
			FROM         b_uks 
					INNER JOIN sv_uks_kat_sem ON b_uks.K_UKS = sv_uks_kat_sem.K_UKS
					INNER JOIN vUksDeti ON b_uks.K_UKS = vUksDeti.K_UKS 			
			WHERE     (b_uks.D_DZ BETWEEN @D_S AND @D_PO) AND (sv_uks_kat_sem.F_OTCHET = 1) AND 
					YEAR(getdate()) -  vUksDeti.GOD_R between 6 and 18
			GROUP BY K_KAT_SEM
		) as t_count_school ON s_kat_sem.K_KAT_SEM = t_count_school.K_KAT_SEM
		where s_kat_sem.K_KAT_SEM_GRUP = @K_KAT_SEM_GRUP
		ORDER  BY C_SORT

		DECLARE @N_KAT_SEM varchar(1024)
		DECLARE @C_SEM_VSEGO int
		DECLARE @C_DETI_VSEGO int
		DECLARE @C_DETI_SCHOOL int
		
		OPEN KatSemCursor			
		FETCH NEXT FROM KatSemCursor into @N_KAT_SEM, @C_SEM_VSEGO, @C_DETI_VSEGO, @C_DETI_SCHOOL 
		-- Цикл по данным 
		while @@FETCH_STATUS = 0
		BEGIN
			SET @C_SEM_VSEGO = ISNULL(@C_SEM_VSEGO,0)
			SET @C_DETI_VSEGO = ISNULL(@C_DETI_VSEGO,0)
			SET @C_DETI_SCHOOL = ISNULL(@C_DETI_SCHOOL,0)
			
			SET @C_SEM_VSEGO_GRUP = @C_SEM_VSEGO_GRUP + @C_SEM_VSEGO
			SET @C_DETI_VSEGO_GRUP = @C_DETI_VSEGO_GRUP + @C_DETI_VSEGO
			SET @C_DETI_SCHOOL_GRUP = @C_DETI_SCHOOL_GRUP + @C_DETI_SCHOOL
			
			-- Для группы  "другие категории семей" данные записываются без детализациии 			
			IF @K_KAT_SEM_GRUP <> 12 
			begin 
				INSERT INTO bo_polgod_sved_sem (K_POLGOD, N_KAT_SEM, C_SEM_VSEGO, C_DETI_VSEGO, C_DETI_SCHOOL, C_SORT)
				VALUES
				(@K_POLGOD, @N_KAT_SEM, @C_SEM_VSEGO, @C_DETI_VSEGO, @C_DETI_SCHOOL, @C_SORT_GRUP*100 + @@CURSOR_ROWS)
			end
					
			FETCH NEXT FROM KatSemCursor into @N_KAT_SEM, @C_SEM_VSEGO, @C_DETI_VSEGO, @C_DETI_SCHOOL 
		END
		DEALLOCATE KatSemCursor
		
		-- Сохраняем данные по группе
		INSERT INTO bo_polgod_sved_sem (K_POLGOD, N_KAT_SEM, C_SEM_VSEGO, C_DETI_VSEGO, C_DETI_SCHOOL, C_SORT)
		VALUES
		(@K_POLGOD, @N_KAT_SEM_GRUP, @C_SEM_VSEGO_GRUP, @C_DETI_VSEGO_GRUP, @C_DETI_SCHOOL_GRUP, @C_SORT_GRUP*100)

		SET @C_SEM_VSEGO_ALL = @C_SEM_VSEGO_ALL + @C_SEM_VSEGO_GRUP
		SET @C_DETI_VSEGO_ALL = @C_DETI_VSEGO_ALL + @C_DETI_VSEGO_GRUP
		SET @C_DETI_SCHOOL_ALL = @C_DETI_SCHOOL_ALL + @C_DETI_SCHOOL_GRUP

		FETCH NEXT FROM KatSemGroupCursor into @K_KAT_SEM_GRUP, @N_KAT_SEM_GRUP, @C_SORT_GRUP
	END

	CLOSE KatSemGroupCursor
	
		-- Сохраняем общие данные
	INSERT INTO bo_polgod_sved_sem (K_POLGOD, N_KAT_SEM, C_SEM_VSEGO, C_DETI_VSEGO, C_DETI_SCHOOL, C_SORT)
	VALUES
	(@K_POLGOD, '1. Всего семей, состоящих на учете в районном управлении социальной защиты из них семьи:', 
		@C_SEM_VSEGO_ALL, @C_DETI_VSEGO_ALL, @C_DETI_SCHOOL_ALL, 0)
 
RETURN
END


go
print 'ALTER VIEW [dbo].[vRepSvedSemList]'
go
ALTER VIEW [dbo].[vRepSvedSemList]
AS
SELECT     dbo.N_PERIOD(D_S, D_PO) AS N_PERIOD, D_ZAP, K_POLGOD, N_RUK, N_ISPOLN, N_HEADER, DATEADD(day, 1, D_PO) AS REP_ON_DATE, 
                      C_POM_MNOGO_DETN, C_POM_NEPOLN, T_INAJA_POM, C_POM_DETI_INVALID
FROM         dbo.bo_polgod
WHERE     (F_DEL = 0)
GO


go
print 'create PROCEDURE dbo.RepSvedSemR3'
go
create PROCEDURE dbo.RepSvedSemR3 
	(
		@D_S datetime,	
		@D_PO datetime,
		@K_POLGOD int
	)
AS
-- Отчет "Сведения о семьях" 
-- Раздел 2
BEGIN
	DECLARE @ludiKpo int
	DECLARE @ludiUks int
	DECLARE @detyKpo int
	DECLARE @detyUks int
		
	
	-- Рассчет данных по количеству КПО, УКС
	set @ludiKpo = (SELECT Count(K_KPO) FROM b_kpo WHERE D_OBRASCH Between @D_S And @D_PO AND F_KONS = 1)
	set @ludiUks = (SELECT count(distinct(K_UKS)) FROM b_uks_kons WHERE D_KONS Between @D_S And @D_PO)
	
	set @detyKpo = (SELECT Count(K_KPO) FROM b_kpo WHERE D_OBRASCH Between @D_S And @D_PO AND F_KONS = 1 
		And GOD_R >= (Year(getdate()) -18))
		
	set @detyUks = (SELECT count(distinct(b_uks_kons.K_UKS)) FROM b_uks_kons 
					INNER JOIN b_klient ON b_uks_kons.K_KLIENT = b_klient.K_KLIENT
					WHERE D_KONS Between @D_S And @D_PO AND b_klient.GOD_R >= (Year(getdate()) -18))

	-- Рассчет кол-ва патронажей
	DECLARE @patr int
	set @patr = (SELECT count(K_UKS) FROM b_uks_patr WHERE isnull(D_NACH,D_OKON) >= @D_S AND 
			(isnull(D_OKON,D_NACH)<= @D_PO))
	
	
	-- Вставка данных раздела 1 
	INSERT INTO bo_polgod_soc_pom (K_POLGOD, N_STROCA, N_SOC_POM, C_LUDI, C_LUDI_DETI, N_PRIM) 
	VALUES(@K_POLGOD, 1, 'Консультационно-информационные услуги, в том числе по телефону (зарегистрированные)',
			@ludiKpo + @ludiUks, @detyKpo + @detyKpo,  
			'Человек '+ cast(@ludiKpo as varchar) + ' (КПО) ' + cast(@ludiUks as varchar) + ' (УКС),' +
			' из них детей ' + cast(@ludiKpo as varchar)+ ' (КПО) ' + cast(@detyUks as varchar) + ' (УКС)')

	-- Вставка данных раздела 2 
	INSERT INTO bo_polgod_soc_pom (K_POLGOD, N_STROCA, N_SOC_POM, C_LUDI, C_LUDI_DETI, N_PRIM) 
	VALUES(@K_POLGOD, 2, 'Поставлено на патронаж',
			@patr, 0,'')

RETURN
END



go
print 'ALTER PROCEDURE dbo.RepSravnitAnalizProvKonsult'
go
ALTER PROCEDURE dbo.RepSravnitAnalizProvKonsult
	(
		@D_S datetime,	
		@D_PO datetime,
		@ERROR_MSG varchar(1024) = '' OUTPUT 	-- строка с текстом ошибки. Если ошибка отсуствовала, то пустая
	)
AS
-- Отчет «Сравнительный анализ проведенных консультаций»
BEGIN

BEGIN TRY
	BEGIN TRAN
	
	DECLARE @K_SRAVNIT_ANALIZ_PROV_KONSULT int
	
	-- Формируем основную запись
	--
	INSERT INTO bo_sravnit_analiz_prov_konsult (D_S, D_PO) VALUES (@D_S, @D_PO)

	SELECT @K_SRAVNIT_ANALIZ_PROV_KONSULT = SCOPE_IDENTITY();
	
	-- Цикл по кварталам
	WHILE  @D_S <= @D_PO
	BEGIN
		DECLARE @kvatlatDateEnd  datetime
		SET @kvatlatDateEnd = DATEADD(day, -1, dbo.NextKvartalDate(@D_S))
		
		if @kvatlatDateEnd > @D_PO 
			set @kvatlatDateEnd = @D_PO
		
		EXECUTE dbo.RepSravnitAnalizProvKonsultKvartal @K_SRAVNIT_ANALIZ_PROV_KONSULT, @D_S, @kvatlatDateEnd
		set @D_S = dbo.NextKvartalDate(@D_S)
	END
	
END TRY
BEGIN CATCH
	
	set @ERROR_MSG = isnull(dbo.GetLastError(),'')
	ROLLBACK TRANSACTION
	RETURN
	
END CATCH;

	COMMIT TRAN
	RETURN
END


go
print 'ALTER PROCEDURE dbo.RepSravnitAnalizProvKonsultKvartal'
go
ALTER PROCEDURE dbo.RepSravnitAnalizProvKonsultKvartal
	(
		@K_SRAVNIT_ANALIZ_PROV_KONSULT int, 
		@D_S datetime,	
		@D_PO datetime
	)
AS
-- Отчет «Сравнительный анализ проведенных консультаций»
-- Формирование отчета непосредственно только по кварталам
BEGIN
	
	DECLARE RepSravnitAnalizProvKonsultKvartalCursor cursor LOCAL READ_ONLY FOR 
	-- Запрос выборки данных
	select s_rab.K_RAB, C_PERV, C_POVT, C_INDIV, C_KONS_KPO
	from s_rab
	LEFT JOIN
	(
		-- даннные "Первичная копсультация" Первичная и Повторная консультации
		select K_RAB, Sum(C_PERV) C_PERV, Sum(C_POVT) as C_POVT
		from 
		(
			SELECT 	vUksKons.K_RAB, cast(F_PERV as int) as C_PERV,
					case when Count(K_UKS_KONS) > 0 then Count(K_UKS_KONS) - F_PERV else 0 end C_POVT 	
			FROM vUksKons
			WHERE	vUksKons.D_KONS between @D_S And  @D_PO AND 
					vUksKons.K_ZAN = 2
			GROUP BY vUksKons.K_UKS, vUksKons.K_KLIENT, vUksKons.K_RAB, F_PERV
		) as t
		GROUP BY K_RAB	
	) as tPervPovt
		ON s_rab.K_RAB = tPervPovt.K_RAB
		
	LEFT JOIN
	(
		-- даннные "Индивидуальное занятие" 
		SELECT	vUksKons.K_RAB, Count(K_UKS_KONS) AS C_INDIV
		FROM	vUksKons
		WHERE	vUksKons.D_KONS between @D_S And @D_PO and
				K_ZAN = 3
		GROUP BY vUksKons.K_RAB
	) as tIndiv
		ON s_rab.K_RAB = tIndiv.K_RAB
		
	LEFT JOIN
	(
		-- даннные "Консултации на КПО" (только C_FAKT, C_TEL, C_PISMO, C_INET т.е. K_OBRASCH in (1,2,4,5))
		SELECT 
				b_kpo.K_SPEC AS K_RAB, count(K_KPO) as C_KONS_KPO
		FROM b_kpo
		WHERE	b_kpo.D_OBRASCH between @D_S And @D_PO AND
				b_kpo.F_KONS = 1 AND K_OBRASCH in (1,2,4,5)
		GROUP BY b_kpo.K_SPEC
	) as tKpoKons
		ON s_rab.K_RAB = tKpoKons.K_RAB

	where	not C_PERV is null	or 
			not C_POVT is null	or 
			not C_INDIV is null or 
			not C_KONS_KPO is null

	DECLARE @K_RAB int
	DECLARE @C_PERV int
	DECLARE @C_POVT int 
	DECLARE @C_INDIV int
	DECLARE @C_KONS_KPO int
	
	OPEN RepSravnitAnalizProvKonsultKvartalCursor			
	FETCH NEXT FROM RepSravnitAnalizProvKonsultKvartalCursor into @K_RAB, @C_PERV, @C_POVT, @C_INDIV, @C_KONS_KPO

	DECLARE @C_KVART int
	DECLARE @C_GOD int	
	SET @C_KVART = DATEPART(QUARTER, @D_S)
	SET @C_GOD = DATEPART(YEAR, @D_S)
	
	-- Цикл по данным
	while @@FETCH_STATUS = 0
	BEGIN
        -- Сохраняем даннные "Первичная копсультация" (K_RAZDEL = 1)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_PERV, @K_RAB, 1)

        -- Сохраняем даннные "Повторная копсультация" (K_RAZDEL = 2)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_POVT, @K_RAB, 2)

        -- Сохраняем даннные "Индивидуальное занятие" (K_RAZDEL = 3)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_INDIV, @K_RAB, 3)
		
        -- Сохраняем даннные "Консултации на КПО" (K_RAZDEL = 4)
		INSERT INTO bo_sravnit_analiz_prov_konsult_ ( K_SRAVNIT_ANALIZ_PROV_KONSULT, C_KVART, C_GOD, C_COUNT, K_RAB, K_RAZDEL)
		values(@K_SRAVNIT_ANALIZ_PROV_KONSULT, @C_KVART, @C_GOD, @C_KONS_KPO, @K_RAB, 4)		
		
		FETCH NEXT FROM RepSravnitAnalizProvKonsultKvartalCursor into @K_RAB, @C_PERV, @C_POVT, @C_INDIV, @C_KONS_KPO
	END
	
	close RepSravnitAnalizProvKonsultKvartalCursor

RETURN
END



go
print 'DROP TABLE [dbo].[XPObjectType]'
go
DROP TABLE [dbo].[XPObjectType]
go

GO
CREATE TABLE [dbo].[ub_role]
(
[K_ROLE] [int] NOT NULL IDENTITY(1, 1),
[N_ROLE] [varchar] (255) COLLATE Cyrillic_General_CI_AS NOT NULL,
[N_DESCRIPTION] [varchar] (255) COLLATE Cyrillic_General_CI_AS NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ub_role] on [dbo].[ub_role]'
GO
ALTER TABLE [dbo].[ub_role] ADD CONSTRAINT [PK_ub_role] PRIMARY KEY CLUSTERED  ([K_ROLE]) ON [PRIMARY]
GO
PRINT N'Creating [dbo].[ub_user]'
GO
CREATE TABLE [dbo].[ub_user]
(
[K_USER] [int] NOT NULL IDENTITY(1, 1),
[N_USER] [varchar] (255) COLLATE Cyrillic_General_CI_AS NULL,
[N_DESCTIONTION] [text] COLLATE Cyrillic_General_CI_AS NULL,
[N_PASSWORD] [varchar] (255) COLLATE Cyrillic_General_CI_AS NULL,
[K_ROLE] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_ub_user] on [dbo].[ub_user]'
GO
ALTER TABLE [dbo].[ub_user] ADD CONSTRAINT [PK_ub_user] PRIMARY KEY CLUSTERED  ([K_USER]) ON [PRIMARY]
GO
PRINT N'Adding foreign keys to [dbo].[ub_user]'
GO
ALTER TABLE [dbo].[ub_user] ADD
CONSTRAINT [FK_ub_user_ub_role] FOREIGN KEY ([K_ROLE]) REFERENCES [dbo].[ub_role] ([K_ROLE])
GO
GO
PRINT N'Creating [dbo].[usv_permission]'
GO
CREATE TABLE [dbo].[usv_permission]
(
[N_PERMISSION] [varchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL,
[K_ROLE] [int] NOT NULL
) ON [PRIMARY]
GO
PRINT N'Creating primary key [PK_usv_permission] on [dbo].[usv_permission]'
GO
ALTER TABLE [dbo].[usv_permission] ADD CONSTRAINT [PK_usv_permission] PRIMARY KEY CLUSTERED  ([N_PERMISSION], [K_ROLE]) ON [PRIMARY]
GO
PRINT N'Adding foreign keys to [dbo].[ub_user]'
GO
ALTER TABLE [dbo].[ub_user] ADD
CONSTRAINT [FK_ub_user_ub_role] FOREIGN KEY ([K_ROLE]) REFERENCES [dbo].[ub_role] ([K_ROLE])
GO
PRINT N'Creating extended properties'
GO
EXEC sp_addextendedproperty N'MS_Description', N'', 'SCHEMA', N'dbo', 'TABLE', N'usv_permission', 'COLUMN', N'N_PERMISSION'
GO
EXEC sp_addextendedproperty N'MS_Description', N'', 'SCHEMA', N'dbo', 'TABLE', N'usv_permission', 'COLUMN', N'K_ROLE'
GO
